/**
 * Created by xc on 2016/05/12.
 */

var common = require('../../common');
var pool = require('../index');
var mysql = require('mysql');
var ItbBaseModel = require('./ItbBaseModel');


// Schema 结构
var EvntPptContestModel = function(obj){
    ItbBaseModel.call(this,obj);

};
EvntPptContestModel.prototype = new ItbBaseModel();

// 取得表名
EvntPptContestModel.prototype.getTableName = function(){
    return 'evnt_ppt_contest';
}
// 取得查询语句中where 后面匹配主键的条件（需要各派生类进行重载）
EvntPptContestModel.prototype.getWhereConditionOfKeyEqual = function(){
    return 'id='+this.id;
}

//------------------------------------------------------------------
// 获取PPT列表
EvntPptContestModel.prototype.getPPT = function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('EvntPptContestModel.getPPT Err:'+err);
        }

        // Use the connection
        var sql = ' SELECT t.id, t.area, t.city, t.campus, c.name AS campus_name, t.index, t.author, t.mobile, t.thumb, t.pages, '+
            ' IFNULL(vt.vote_cnt,0) AS all_vote_cnt, IFNULL(vt2.vote_cnt,0) AS user_vote_cnt '+
            ' FROM evnt_ppt_contest t '+
            ' INNER JOIN tg_campus c ON c.id=t.campus AND c.del_flg=0 '+
            ' LEFT JOIN '+
            ' (SELECT v.ppt_id, COUNT(*) AS vote_cnt FROM evnt_ppt_vote v '+
            '   WHERE v.del_flg=0 AND v.user_id>0 AND v.ppt_id='+THIS.id+' GROUP BY v.ppt_id) vt '+
            ' ON t.id = vt.ppt_id '+
            ' LEFT JOIN '+
            ' (SELECT v2.ppt_id, COUNT(*) AS vote_cnt FROM evnt_ppt_vote v2 '+
            '   WHERE v2.del_flg=0 AND v2.user_id='+THIS.user_id+' AND v2.ppt_id='+THIS.id+
            '     AND v2.update_time >= CURDATE() AND v2.update_time < DATE_ADD(CURDATE(),INTERVAL 24 HOUR) '+// 取用户今天之内的投票情况
            '   GROUP BY v2.ppt_id) vt2 '+
            ' ON t.id = vt2.ppt_id '+
            ' WHERE t.del_flg = 0 AND t.id='+THIS.id;

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            console.log();

            callback(error, result);
        });
    });
}

//------------------------------------------------------------------
// 获取PPT列表
EvntPptContestModel.prototype.getPptList = function(callback, skipCnt, pageSize){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('EvntPptContestModel.getPptList Err:'+err);
        }

        // Use the connection
        var sql = ' SELECT t.id, t.area, t.city, t.campus, c.name AS campus_name, t.index, t.author, t.mobile, t.thumb, '+
            ' IFNULL(vt.vote_cnt,0) AS all_vote_cnt, IFNULL(vt2.vote_cnt,0) AS user_vote_cnt '+
            ' FROM evnt_ppt_contest t '+
            ' INNER JOIN tg_campus c ON c.id=t.campus AND c.del_flg=0 '+
            ' LEFT JOIN '+
            ' (SELECT v.ppt_id, COUNT(*) AS vote_cnt FROM evnt_ppt_vote v WHERE v.del_flg=0 AND v.user_id>0 GROUP BY v.ppt_id) vt '+
            ' ON t.id = vt.ppt_id '+
            ' LEFT JOIN '+
            ' (SELECT v2.ppt_id, COUNT(*) AS vote_cnt FROM evnt_ppt_vote v2 '+
            '   WHERE v2.del_flg=0 AND v2.user_id='+THIS.user_id+
            '     AND v2.update_time >= CURDATE() AND v2.update_time < DATE_ADD(CURDATE(),INTERVAL 24 HOUR) '+// 取用户今天之内的投票情况
            '   GROUP BY v2.ppt_id) vt2 '+
            ' ON t.id = vt2.ppt_id '+

            ' WHERE t.del_flg = 0 ';
        if (Number(THIS.area)>0) {
            sql += ' AND t.area= ' + THIS.area;
        }
        if (Number(THIS.city)>0) {
            sql += ' AND t.city= ' + THIS.city;
        }
        if (Number(THIS.campus)>0) {
            sql += ' AND t.campus= ' + THIS.campus;
        }

        if (THIS.author) {
            sql += ' AND t.author LIKE "%' + THIS.author + '%"';
        }
        if (Number(THIS.mobile)>0) {
            sql += ' AND t.mobile= ' + THIS.mobile;
        }

        if (THIS.order_by) {
            sql += ' ORDER BY ' + THIS.order_by;
        } else {
            sql += ' ORDER BY t.area, t.city, t.campus, t.author, t.update_time DESC ';
        }

        if (typeof skipCnt!='undefined' && skipCnt != null && typeof pageSize!='undefined' && pageSize != null){
            sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        }

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            console.log();

            callback(error, result);
        });
    });
}

//------------------------------------------------------------------
// 获取最大index
EvntPptContestModel.prototype.getMaxIdx = function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('EvntPptContestModel.getCampusIdx Err:'+err);
        }

        // Use the connection
        var sql = ' SELECT IFNULL(max(t.index),0) AS max_idx FROM evnt_ppt_contest t WHERE t.del_flg=0 ';
        if (Number(THIS.area)>0) {
            sql += ' AND t.area= ' + THIS.area;
        }
        if (Number(THIS.city)>0) {
            sql += ' AND t.city= ' + THIS.city;
        }
        if (Number(THIS.campus)>0) {
            sql += ' AND t.campus= ' + THIS.campus;
        }

        if (THIS.author) {
            sql += ' AND t.author LIKE "%' + THIS.author + '%"';
        }
        if (Number(THIS.mobile)>0) {
            sql += ' AND t.mobile= ' + THIS.mobile;
        }

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            console.log();

            if(result && result.length>0) {
                callback(error, Number(result[0].max_idx || '0'));
            } else {
                callback(error, 0)
            }
        });
    });
}

//------------------------------------------------------------------
// 获取PPT列表
EvntPptContestModel.prototype.getByOrder = function(callback, limit){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('EvntPptContestModel.getPptList Err:'+err);
        }

        // Use the connection
        var sql = ' SELECT t.id, t.author, t.mobile, t.email, t.wechat, t.thumb, t.file_name, t.file_url, '+
            ' DATE_FORMAT(t.create_time, "%Y/%m/%d %H:%i:%S") AS create_time, '+
            ' DATE_FORMAT(t.update_time, "%Y/%m/%d %H:%i:%S") AS update_time '+
            ' FROM evnt_ppt_contest t '+
            ' WHERE t.del_flg = 0 '+
            ' AND t.thumb IS NOT NULL AND t.thumb != "" ';

        if (THIS.order_by) {
            sql += ' ORDER BY ' + THIS.order_by;
        } else {
            sql += ' ORDER BY t.update_time DESC ';
        }

        sql += " limit " + Number(limit||'10');

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            console.log();

            callback(error, result);
        });
    });
}

// 导出
module.exports = EvntPptContestModel;